package birs.controller.expanditure.task;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import birs.model.expanditure.XPD005Model;
import birs.model.expanditure.XPD005ModelA;
import birs.model.expanditure.XPD005ModelB;
import birs.model.expanditure.XPD005ModelC;
import birs.util.ConnectionDB;
import birs.util.ConvertDataType;
import birs.util.ResultSetMapper;

public class XPD005Task {
	public void getExpenditureSalary(HttpServletRequest request,HttpServletResponse response,String periodYear){
		List<XPD005ModelA> xpd005ModelAList = null;
		List<XPD005ModelB> xpd005ModelBList = null;
		try(Connection con = ConnectionDB.getMisConnection()){
			String periodQueryStr = "BETWEEN "+(Integer.parseInt(periodYear)-1)+"10 AND "+periodYear+"09";
			//periodYear = "BETWEEN "+(Integer.parseInt(periodYear)-1)+"10 AND "+periodYear+"09";
			ResultSetMapper<XPD005ModelA> xpd005ModelA = new ResultSetMapper<XPD005ModelA>();
			xpd005ModelAList = xpd005ModelA.mapRersultSetToObject(getExpenditureSQL(con,periodQueryStr), XPD005ModelA.class);
			ResultSetMapper<XPD005ModelB> xpd005ModelB = new ResultSetMapper<XPD005ModelB>();
			xpd005ModelBList = xpd005ModelB.mapRersultSetToObject(getExpenditureByStaffTypeSQL(con,periodQueryStr), XPD005ModelB.class);
			List<XPD005ModelB> list = null;
			double sum = 0;
			for (XPD005ModelA xpd005ModelATmp : xpd005ModelAList) {
				list = new ArrayList<XPD005ModelB>();
				for(XPD005ModelB xpd005ModelBTmp : xpd005ModelBList){
					if(xpd005ModelATmp.getBudgetCode() == xpd005ModelBTmp.getBudgetCode()){
						list.add(xpd005ModelBTmp);
						sum += xpd005ModelBTmp.getAmount();
					}
				}
				xpd005ModelATmp.setChildren(list);
			}
			XPD005Model xpd005model = new XPD005Model();
			xpd005model.setName("งบประมาณปี "+periodYear);
			xpd005model.setAmount(sum);
			xpd005model.setChildren(xpd005ModelAList);
			String resultJson = ConvertDataType.getInstance().objectToJasonArray(xpd005model);
			sendResponse(request, response, resultJson);
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public void getRadioBox(HttpServletRequest request,HttpServletResponse response){
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD005ModelC> xpd005ModelC = new ResultSetMapper<XPD005ModelC>();
			List<XPD005ModelC> xpdModelCList = xpd005ModelC.mapRersultSetToObject(getRadioBoxSQL(con), XPD005ModelC.class);
			String resultJson = ConvertDataType.getInstance().objectToJasonArray(xpdModelCList);
			sendResponse(request, response, resultJson);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private ResultSet getExpenditureSQL(Connection con,String periodYear){
		String sql = " 	SELECT ST.STAFFTYPESUB3 AS BUDGETCODE , SBD.BYTEDES AS BUDGETNAME, SUM(SICI.NETAMOUNT) AS AMOUNT	"
				+" 	FROM STAFF S, STAFFINCOMEITEM SICI,STAFFTYPE ST,SYSBYTEDES SBD	"
				+" 	WHERE S.STAFFSTATUS IN (10,20,21,22,69,70,99)	"
				+" 	AND S.STAFFTYPEID = ST.STAFFTYPEID	"
				+" 	AND ST.STAFFTYPESUB3 IN (1,2)	"
				+" 	AND S.STAFFID = SICI.STAFFID	"
				+" 	AND SICI.FUNDACCID < 2000	"
				+" 	AND SICI.PERIODID "+periodYear
				+" 	AND SBD.TABLENAME = 'STAFF'	"
				+" 	AND SBD.COLUMNNAME = 'STAFFTYPESUB3'	"
				+" 	AND SBD.BYTECODE = ST.STAFFTYPESUB3	"
				+" 	GROUP BY ST.STAFFTYPESUB3,SBD.BYTEDES,ST.STAFFTYPESUB3";
		return getData(sql,con);
	}
	
	private ResultSet getExpenditureByStaffTypeSQL(Connection con,String periodYear){
		String sql = " 	SELECT ST.STAFFTYPESUB3 AS BUDGETCODE ,SBD.BYTEDES AS BUDGETNAME, 'สายวิชาการ' AS STAFFTYPE, SUM(SICI.NETAMOUNT) AS AMOUNT	"
				+" 	FROM STAFF S, STAFFINCOMEITEM SICI,STAFFTYPE ST,SYSBYTEDES SBD	"
				+" 	WHERE S.STAFFSTATUS IN (10,20,21,22,69,70,99)	"
				+" 	AND S.STAFFTYPEID = ST.STAFFTYPEID	"
				+" 	AND ST.STAFFTYPESUB3 IN (1,2)	"
				+" 	AND S.STAFFID = SICI.STAFFID	"
				+" 	AND SICI.FUNDACCID < 2000	"
				+" 	AND SICI.PERIODID "+periodYear
				+" 	AND SBD.TABLENAME = 'STAFF'	"
				+" 	AND SBD.COLUMNNAME = 'STAFFTYPESUB3'	"
				+" 	AND SBD.BYTECODE = ST.STAFFTYPESUB3	"
				+" 	AND S.ACADPOSITIONID IS NOT NULL	"
				+" 	GROUP BY SBD.BYTEDES,ST.STAFFTYPESUB3	"
				+" 		"
				+" 	UNION ALL	"
				+" 		"
				+" 	SELECT ST.STAFFTYPESUB3 AS BUDGETCODE, SBD.BYTEDES AS BUDGETNAME, 'สายสนับสนุน' AS STAFFTYPE, SUM(SICI.NETAMOUNT) AS AMOUNT	"
				+" 	FROM STAFF S, STAFFINCOMEITEM SICI,STAFFTYPE ST,SYSBYTEDES SBD	"
				+" 	WHERE S.STAFFSTATUS IN (10,20,21,22,69,70,99)	"
				+" 	AND S.STAFFTYPEID = ST.STAFFTYPEID	"
				+" 	AND ST.STAFFTYPESUB3 IN (1,2)	"
				+" 	AND S.STAFFID = SICI.STAFFID	"
				+" 	AND SICI.FUNDACCID < 2000	"
				+" 	AND SICI.PERIODID BETWEEN 255610 AND 255709	"
				+" 	AND SBD.TABLENAME = 'STAFF'	"
				+" 	AND SBD.COLUMNNAME = 'STAFFTYPESUB3'	"
				+" 	AND SBD.BYTECODE = ST.STAFFTYPESUB3	"
				+" 	AND S.ACADPOSITIONID IS NULL	"
				+" 	GROUP BY SBD.BYTEDES,ST.STAFFTYPESUB3 ";

		return getData(sql, con);
	}
	
	private ResultSet getRadioBoxSQL(Connection con) throws SQLException{
		String sql = " 	SELECT D.BUDGETPERIODID "
				+" 	FROM PROMIS.v_bud_linktype D	"
				+" 	WHERE D.BUDGETPERIODID <= (SELECT TO_CHAR(SYSDATE, 'yyyy', 'NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI') AS YEAR FROM DUAL)	"
				+"		  AND D.BUDGETPERIODID >= 2555"
				+" 	GROUP BY D.BUDGETPERIODID	"
				+" 	ORDER BY D.BUDGETPERIODID DESC";
		return getData(sql,con);
	}
	
	private void sendResponse(HttpServletRequest request,HttpServletResponse response,String resultJson) throws IOException{
		response.setCharacterEncoding("utf-8");
		response.setContentType("application/json;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.write(resultJson);
		out.close();
	}
	
	private ResultSet getData(String sql,Connection con){
		ResultSet result = null;
		try {
			Statement statement = con.createStatement();
			result = statement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
}